package book;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class UpdateServlet extends HttpServlet {

	public void doGet(HttpServletRequest request,HttpServletResponse response)
			throws ServletException, IOException {

//　入力パラメータの文字エンコーディング
		request.setCharacterEncoding("UTF-8");

//　入力パラメータを取得

//セッション情報に保存された、担当者IDを取得する
	    HttpSession session = request.getSession();
	    String user_id = (String)session.getAttribute("user_id");

//更新時刻を取得
		Calendar calendar = Calendar.getInstance();

		int year = calendar.get(Calendar.YEAR);
	    int month = calendar.get(Calendar.MONTH) + 1;
	    int day = calendar.get(Calendar.DATE);
	    int hour =calendar.get(Calendar.HOUR_OF_DAY);
	    int minute = calendar.get(Calendar.MINUTE);
	    int second = calendar.get(Calendar.SECOND);

	    String time = (year + "-" + month + "-" + day + "  " + hour + ":" + minute + ":" + second);

//　変更するレコードをidで指定
		String id = request.getParameter( "id" );

		String title = request.getParameter( "title" );
		String writer = request.getParameter( "writer" );
		String publisher_id = request.getParameter( "publisher" );

		System.out.println("1:" + publisher_id);

		String genre_id = request.getParameter( "genre_id" );
		String isbn = request.getParameter( "isbn" );
		String publication_date = request.getParameter( "publication_date" );
		String open_flag_boolean = request.getParameter( "open_flag" );
		String open_flag = "";
		if(open_flag_boolean.equals("true")){
			open_flag = "1";	// 公開
		}else{
			open_flag = "0";	// 非公開
		}
		String content = request.getParameter( "content" );
		String picture = request.getParameter( "picture" );

//　SQL文を記述
		String update_sql_books = "UPDATE books SET title = ?, writer = ?, publisher_id = ?, genre_id = ?, isbn = ?, publication_date = ?, open_flag = ?, content = ? WHERE id = " + id;
		String update_sql_updates = "INSERT INTO updates (update_date, manager_id) VALUE(?, ?)";

		Connection con = null;
		Statement smt = null;

		String path = null;

		try{
//　データベースを接続するためのConnectionオブジェクトを取得
//　SQLを発行するためのStatementオブジェクトを取得
		con = common.DBManager.getConnection();
		smt = con.createStatement();

//　prepareStatement()メソッド　：　引数にSQL文を指定
		PreparedStatement ps = con.prepareStatement(update_sql_books);
		PreparedStatement ps_updates = con.prepareStatement(update_sql_updates);

//　SQL文のパラメータ変数(?)に値をセット
		ps.setString( 1, title );
		ps.setString( 2, writer );
		ps.setInt( 3, this.Publisher_id(publisher_id) );
		System.out.println(this.Publisher_id(publisher_id));
		ps.setInt( 4, Integer.parseInt(genre_id) );
		ps.setString( 5, isbn );
		ps.setString( 6, publication_date );
		ps.setInt( 7, Integer.parseInt(open_flag) );
		ps.setString( 8, content );

		ps_updates.setString(1, time);
		ps_updates.setInt(2,Integer.parseInt(user_id) );

//　executeUpdate()メソッドでSQLの発行
//　※引数はないので注意
		ps.executeUpdate();
		ps_updates.executeUpdate();

		request.setAttribute("msg", "変更しました");
		path = "/book/SearchServlet";

		}catch( SQLException e ){
//=====▼======	一覧へ遷移後のメッセージを追加	2013.10.17	矢野=====▼======
			//	throw new ServletException(e);
				//　エラーメッセージを格納するArrayListの生成
				ArrayList<String> error_message = new ArrayList<String>();
				error_message.add("発行年月日が存在しません");

				BookBean bean = new BookBean();
				bean.setId(Integer.parseInt(id));
				bean.setTitle(title);
				bean.setWriter(writer);
			//	bean.setPublisherId(Integer.parseInt(publisher_id));
				try {
					bean.setPublisherId(this.Publisher_id(publisher_id));
				} catch (SQLException e1) {
					//
					e1.printStackTrace();
				}
				bean.setGenreId(Integer.parseInt(genre_id));
				bean.setIsbn(isbn);
				bean.setPublicationDate(publication_date);
				bean.setOpenFlag(Integer.parseInt(open_flag));
				bean.setContent(content);
				bean.setPicture(picture);

				request.setAttribute("bean", bean);

				String error_publication_date = "error";
				request.setAttribute( "error_message" , error_message );
				request.setAttribute( "error_publication_date" , error_publication_date );

				path = "/book/userBookEdit.jsp";
//=====▲======	一覧へ遷移後のメッセージを追加	2013.10.17	矢野=====▲======
			}finally{

			if( smt != null ){

				try{
					smt.close();
				}catch( SQLException ignore ){
					//　何もしない
				}
			}
			if( con != null ){

				try{
					con.close();
				}catch( SQLException ignore ){
					//　何もしない
				}
			}
		}

//	JSPに画面遷移、フォワード、書籍検索結果へ遷移する。
		request.getRequestDispatcher(path).forward(request, response);
	}

	//　出版者IDを取得する
	public int Publisher_id(String publisher) throws SQLException{

		Statement smt 		= null;
		Connection con 		= null;

		int publisher_id 		= 9999999;
		con = common.DBManager.getConnection();
		smt = con.createStatement();

		// 書籍情報取得SQL文
		//　入力された出版者のIDをpublishersテーブルから取得
		String sql = "select * from publishers  where NAME = '" + publisher + "'";

		String sqlCnt = "select count(*) as cnt from publishers where NAME = '" + publisher + "'";

		ResultSet rsCnt = smt.executeQuery(sqlCnt);

		if(rsCnt.next()){
			int coung = rsCnt.getInt("cnt");
			rsCnt.close();
			//　出版者がDBになかった場合新しく作成すしてIDを取得
			if(coung == 0){
				String sql_max = "select max(ID) from publishers";
				ResultSet rs_max = smt.executeQuery(sql_max);

				while(rs_max.next())
				{
					publisher_id = rs_max.getInt("MAX(ID)") + 1;
				}

				String sql_new_publisher = "insert into publishers(ID,NAME) " +
						"value(?,?)";
				PreparedStatement ist_newest = con.prepareStatement(sql_new_publisher);

				ist_newest.setInt(1,publisher_id);
				ist_newest.setString(2,publisher);
				ist_newest.executeUpdate();
				rs_max.close();

			}else{
				ResultSet rs = smt.executeQuery(sql);
				while(rs.next())
				{
				publisher_id = rs.getInt("ID");
				}

			}

			smt.close();
			con.close();
		}
		return publisher_id;
	}
}
